﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.Collections.Generic;
using Facade.Excel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace PeisongWeb.admin
{
    public partial class financeSelfHelp : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                companyBind();
            }
        }

        private void companyBind()
        {
            DataTable dt = logic.company.listWithCompanyType("2");
            storeCompany.DataSource = dt;
            storeCompany.DataBind();
            cboCompany.ValueField = "companyId";
            cboCompany.DisplayField = "company";
            cboCompany.DataBind();
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            if (txtStartDt.SelectedDate != DateTime.MinValue && txtEndDt.SelectedDate != DateTime.MinValue)
            {
                StringBuilder where = new StringBuilder();
                where.AppendFormat("companyId={0}", cboCompany.SelectedItem.Value);
                where.AppendFormat(" AND DATEDIFF(DAY,receiveDt,'{0}')<=0 AND DATEDIFF(DAY,receiveDt,'{1}')>=0", txtStartDt.SelectedDate.ToString("yyyy-MM-dd"), txtEndDt.SelectedDate.ToString("yyyy-MM-dd"));

                DataSet ds = DAL.DbHelper.ExecuteReDataSet("[dbo].[p_PaymentHistory_ManualExportExcel]", where.ToString());

                DataTable dt = ds.Tables[0];
                dt.Columns[0].ColumnName = "商品名称";
                dt.Columns[1].ColumnName = "数量";
                dt.Columns[2].ColumnName = "单价";
                dt.Columns[3].ColumnName = "单位";
                dt.Columns[4].ColumnName = "小计";
                dt.Columns[5].ColumnName = "订单号";
                dt.Columns[6].ColumnName = "订单状态";
                dt.Columns[7].ColumnName = "交易成功日期";
                dt.Columns[8].ColumnName = "收货日期";

                Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
                formatOptions.Add(1, FormatStyle.ToFix2);
                formatOptions.Add(2, FormatStyle.ToFix2);
                formatOptions.Add(4, FormatStyle.ToFix2);
                formatOptions.Add(7, FormatStyle.DateTime);

                List<int> footerSumColumns = new List<int>()
                {
                    4
                };
                List<int> mergeColumnIndexs = new List<int>()
                {
                    5,6,7,8
                };

                string title = string.Format("{0} {1} 至 {2} 账期订单数据", cboCompany.SelectedItem.Text, txtStartDt.SelectedDate.ToString("yyyy-MM-dd"), txtEndDt.SelectedDate.ToString("yyyy-MM-dd"));

                ExportFacade facade = new ExportFacade();

                HSSFWorkbook workbook = facade.InitializeWorkbook("杭州农副产品物流网络科技有限公司", "tianzh", "采购配送系统", "财务账单订单数据");

                Sheet sheet1 = workbook.CreateSheet("订单详细");

                facade.CreateRowHeader(workbook, sheet1, title, dt);
                facade.FillRowData(workbook, sheet1, 2, dt, formatOptions, null, footerSumColumns, null);

                // 小类汇总
                DataTable dt2 = ds.Tables[2];
                dt2.Columns[0].ColumnName = "类别";
                dt2.Columns[1].ColumnName = "金额";

                int startRowIndex = 2 + dt.Rows.Count + 1 + 5;

                #region 创建标题行

                Row titleRow = sheet1.CreateRow(startRowIndex);
                titleRow.HeightInPoints = 25;
                titleRow.CreateCell(0).SetCellValue("分类汇总信息");

                CellStyle titleStyle = workbook.CreateCellStyle();
                titleStyle.Alignment = HorizontalAlignment.CENTER;
                Font titleFont = workbook.CreateFont();
                titleFont.FontHeightInPoints = 20;
                titleFont.Boldweight = 700;
                titleStyle.SetFont(titleFont);
                titleStyle.WrapText = true;

                titleRow.GetCell(0).CellStyle = titleStyle;
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));

                Row headerRow = sheet1.CreateRow(startRowIndex + 1);

                CellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                Font font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);

                foreach (DataColumn column in dt2.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                }
                #endregion

                facade.FillRowData(workbook, sheet1, startRowIndex + 2, dt2, null, null, null, null);

                facade.ExportByWeb(workbook, title, title + ".xls");
            }
        }
    }
}
